package Data;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class GameData 
{
	Connection conn;

	private void populatePlayerIdByName(Player player) throws GameException
	{
		PreparedStatement stmt = null;
		ResultSet resultSet = null;
		PreparedStatement insertStmt = null;
		ResultSet resultSetId = null;
		
		try
		{
			int id = -1;
			
			stmt = conn.prepareStatement("SELECT player_id FROM game_players WHERE name = ?");
			stmt.setString(1, player.getName());
			
			resultSet = stmt.executeQuery();
			
			if (resultSet.next())
			{
				id = resultSet.getInt(1);
			}
			else
			{
				insertStmt = conn.prepareStatement("INSERT INTO game_players(name) VALUES(?)", Statement.RETURN_GENERATED_KEYS);
				insertStmt.setString(1, player.getName());
				insertStmt.executeUpdate();
				
				resultSetId = insertStmt.getGeneratedKeys();
				resultSetId.next();
				id = resultSetId.getInt(1);
			}
			
			player.setId(id);
		}
		catch (SQLException e)
		{
			throw new GameException(e);
		}
		finally
		{
			try
			{
				if (resultSet != null)
					resultSet.close();
				
				if (stmt != null)
					stmt.close();
				
				if (resultSetId != null)
					resultSetId.close();
				
				if (insertStmt != null)
					insertStmt.close();
			}
			catch (SQLException e)
			{
				throw new GameException(e);
			}
		}
	}

	public int startNewGame(Player player1, Player player2) throws GameException
	{
		PreparedStatement stmt = null;
		ResultSet resultSet = null;
		
		try
		{
			conn = ConnectionPool.getConnection();
			
			stmt = conn.prepareStatement(
					"INSERT INTO game(player1_id, player2_id) VALUES(?, ?)", Statement.RETURN_GENERATED_KEYS);
			
			if (player1.getId() == null)
				populatePlayerIdByName(player1);
			
			if (player2.getId() == null)
				populatePlayerIdByName(player2);
			
			stmt.setInt(1, player1.getId());
			stmt.setInt(2, player2.getId());
			stmt.executeUpdate();
			
			resultSet = stmt.getGeneratedKeys();
			resultSet.next();
			
			return resultSet.getInt(1);
		}
		catch (SQLException e)
		{
			throw new GameException(e);
		}
		finally
		{
			try
			{
				if (resultSet != null)
					resultSet.close();
				
				if (stmt != null)
					stmt.close();
				
				if (conn != null)
					conn.close();
			}
			catch (SQLException e)
			{
				throw new GameException(e);
			}
		}
	}
	
	private void insertArmy(int stateId, Player player) throws GameException
	{
		PreparedStatement stmt = null;
		
		try
		{
			stmt = conn.prepareStatement(
						"INSERT INTO game_army(state_id, player_id, character_id, dead, health) " +
						"VALUES(?, ?, ?, ? , ?)", Statement.RETURN_GENERATED_KEYS);
			
			for (Character character : player.getArmy())
			{
				stmt.setInt(1, stateId);
				stmt.setInt(2, player.getId());
				stmt.setInt(3, character.getId());
				stmt.setBoolean(4, character.isDead());
				stmt.setInt(5, character.getHealth());
				stmt.addBatch();
			}
			
			stmt.executeBatch();
		}
		catch (SQLException e)
		{
			throw new GameException(e);
		}
		finally
		{
			try
			{
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				throw new GameException(e);
			}
		}
	}
	
	/**
	 * Save game state to database
	 * @param gameId: game id
	 * @param player1: player 1
	 * @param player2: player 2
	 * @param stateData: board state blob
	 * @throws GameException
	 */
	public void saveState(int gameId, Player player1, Player player2, byte[] stateData) throws GameException
	{
		PreparedStatement stmt = null;
		ResultSet resultSet = null;
		
		try
		{
			conn = ConnectionPool.getConnection();
			conn.setAutoCommit(false);
			conn.setSavepoint();
			
			stmt = conn.prepareStatement(
					"INSERT INTO game_state(game_id, state) VALUES(?, ?)",
					Statement.RETURN_GENERATED_KEYS);
			
			stmt.setInt(1, gameId);
			stmt.setBlob(2, new ByteArrayInputStream(stateData, 0, stateData.length));
			stmt.executeUpdate();
			
			resultSet = stmt.getGeneratedKeys();
			resultSet.next();
			int stateId = resultSet.getInt(1);
			
			insertArmy(stateId, player1);
			insertArmy(stateId, player2);
			
			conn.commit();
		}
		catch (SQLException e)
		{
			throw new GameException(e);
		}
		finally
		{
			try
			{
				if (resultSet != null)
					resultSet.close();
				
				if (stmt != null)
					stmt.close();
				
				if (conn != null)
				{
					conn.setAutoCommit(true);
					conn.close();
				}
			}
			catch (SQLException e)
			{
				throw new GameException(e);
			}
		}
	}
	
	/**
	 * Get game-info of latest played game
	 * @return GameInfo for latest game or null if not found
	 * @throws GameException
	 */
	public GameInfo getLatestGame() throws GameException
	{
		PreparedStatement stmtSelectLatestGame = null;
		ResultSet resultSet = null;
		
		try
		{
			conn = ConnectionPool.getConnection();
			
			stmtSelectLatestGame = conn.prepareStatement(
					"SELECT game.game_id, state_id, player1_id, player2_id, start_time FROM game, game_state " +
					"WHERE game.game_id = game_state.game_id " +
					"ORDER BY update_time " +
					"DESC LIMIT 1");
			
			resultSet = stmtSelectLatestGame.executeQuery();
			
			if (resultSet.first())
			{
				return new GameInfo(resultSet.getInt(1), 
									resultSet.getInt(2),
									resultSet.getInt(3), 
									resultSet.getInt(4), 
									resultSet.getTimestamp(5));
			}
			
			return null;
		}
		catch (SQLException e)
		{
			throw new GameException(e);
		}
		finally
		{
			try
			{
				if (resultSet != null)
					resultSet.close();
				
				if (stmtSelectLatestGame != null)
					stmtSelectLatestGame.close();
				
				if (conn != null)
					conn.close();
			}
			catch (SQLException e)
			{
				throw new GameException(e);
			}
		}
	}

	/**
	 * Load game state from database
	 * @param stateId: state id to load
	 * @param player1: player 1 to populate
	 * @param player2: player 2 to populate
	 * @param stateData: board state blob to populate (sizeOfBoard^2 bytes)
	 * @return true if state loaded successfully
	 * @throws GameException
	 */
	public boolean loadState(int stateId, Player player1, Player player2, byte[] stateData) 
		throws GameException
	{
		PreparedStatement stmtSelectStateData = null;
		PreparedStatement stmtSelectStatePlayerDetails = null;
		PreparedStatement stmtSelectStatePlayerArmy = null;
		ResultSet resultSet = null;
		
		try
		{
			conn = ConnectionPool.getConnection();
			
			// get state data
			stmtSelectStateData = conn.prepareStatement("SELECT state_id, game_id, state FROM game_state WHERE state_id=?");
			stmtSelectStateData.setInt(1, stateId);
			
			resultSet = stmtSelectStateData.executeQuery();
			
			if (resultSet.next())
			{
				InputStream stream = resultSet.getBinaryStream(3);
				stream.read(stateData);
			}
			else
			{
				return false;
			}
			
			resultSet.close();
			resultSet = null;
			
			stmtSelectStateData.close();
			stmtSelectStateData = null;
			
			// get player info
			stmtSelectStatePlayerDetails = conn.prepareStatement(
					"SELECT game_players.player_id, game_players.name " +
					"FROM game, game_state, game_players " +
					"WHERE " +
						"game_state.state_id = ? AND " +
						"game.game_id = game_state.game_id AND " +
						"(game_players.player_id = game.player1_id OR game_players.player_id = game.player2_id)");
			
			stmtSelectStatePlayerDetails.setInt(1, stateId);
			
			resultSet = stmtSelectStatePlayerDetails.executeQuery();
			
			if (resultSet.next())
			{
				player1.setId(resultSet.getInt(1));
				player1.setName(resultSet.getString(2));
				
				if (!resultSet.next())
					return false;
				
				player2.setId(resultSet.getInt(1));
				player2.setName(resultSet.getString(2));
			}
			else
			{
				return false;
			}
			
			resultSet.close();
			resultSet = null;
			
			stmtSelectStatePlayerDetails.close();
			stmtSelectStatePlayerDetails = null;
			
			// get both players' armies
			stmtSelectStatePlayerArmy = conn.prepareStatement(
					"SELECT game_army.character_id, game_army.dead, game_army.health " +
					"FROM game_state, game_army " +
					"WHERE " +
						"game_state.state_id = ? AND " +
						"game_army.player_id = ? AND " +
						"game_state.state_id = game_army.state_id");
			
			// get player1 army
			stmtSelectStatePlayerArmy.setInt(1, stateId);
			stmtSelectStatePlayerArmy.setInt(2, player1.getId());
			resultSet = stmtSelectStatePlayerArmy.executeQuery();
			int i = 0;
			
			while (resultSet.next())
			{
				Character c = DbInterface.getCharacterFromID(resultSet.getInt(1));
				
				c.setIsDead(resultSet.getBoolean(2));
				c.setHealth(resultSet.getInt(3));
				
				player1.setArmy(c, i++);
			}
			
			resultSet.close();
			resultSet = null;
			
			// get player2 army
			stmtSelectStatePlayerArmy.setInt(1, stateId);
			stmtSelectStatePlayerArmy.setInt(2, player2.getId());
			resultSet = stmtSelectStatePlayerArmy.executeQuery();
			i = 0;
			
			while (resultSet.next())
			{
				Character c = DbInterface.getCharacterFromID(resultSet.getInt(1));
				
				c.setIsDead(resultSet.getBoolean(2));
				c.setHealth(resultSet.getInt(3));
				
				player2.setArmy(c, i++);
			}
			
			resultSet.close();
			resultSet = null;
			
			stmtSelectStatePlayerArmy.close();
			stmtSelectStatePlayerArmy = null;
			
			return true;
		}
		catch (QueryException e)
		{
			throw new GameException(e);
		}
		catch (SQLException e)
		{
			throw new GameException(e);
		}
		catch (IOException e)
		{
			throw new GameException(e);
		}
		finally
		{
			try
			{
				if (resultSet != null)
					resultSet.close();
				
				if (stmtSelectStateData != null)
					stmtSelectStateData.close();
				
				if (stmtSelectStatePlayerDetails != null)
					stmtSelectStatePlayerDetails.close();
				
				if (stmtSelectStatePlayerArmy != null)
					stmtSelectStatePlayerArmy.close();
				
				if (conn != null)
					conn.close();
			}
			catch (SQLException e)
			{
				throw new GameException(e);
			}
		}
	}
	
	/**
	 * Get player id by name (insert if doesn't exist)
	 * @param playerName: player name
	 * @return player id
	 * @throws GameException
	 */
	public static int getPlayerIdByName(String playerName) throws GameException
	{
		Connection conn = null;
		PreparedStatement stmtSelectPlayerByName = null;
		ResultSet resultSet = null;
		PreparedStatement stmtInsertPlayer = null;
		ResultSet resultSetId = null;
		
		try
		{
			conn = ConnectionPool.getConnection();
			
			stmtSelectPlayerByName = conn.prepareStatement("SELECT player_id FROM game_players WHERE name = ?;");
			stmtSelectPlayerByName.setString(1, playerName);
			
			resultSet = stmtSelectPlayerByName.executeQuery();
			
			if (resultSet.first())
				return resultSet.getInt(1);
			
			stmtInsertPlayer = conn.prepareStatement("INSERT INTO game_players(name) VALUES(?);", Statement.RETURN_GENERATED_KEYS);
			stmtInsertPlayer.setString(1, playerName);
			stmtInsertPlayer.executeUpdate();
			
			resultSetId = stmtInsertPlayer.getGeneratedKeys();
			resultSetId.first();
			
			return resultSetId.getInt(1);
		}
		catch (SQLException e)
		{
			throw new GameException(e);
		}
		finally
		{
			try
			{
				if (resultSetId != null)
					resultSetId.close();
				
				if (stmtInsertPlayer != null)
					stmtInsertPlayer.close();
				
				if (resultSet != null)
					resultSet.close();
				
				if (stmtSelectPlayerByName != null)
					stmtSelectPlayerByName.close();
				
				if (conn != null)
					conn.close();
			}
			catch (SQLException e)
			{
				throw new GameException(e);
			}
		}
	}
}
